﻿using FOModels.FinancialLeasingLoan;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess.FinancialLeasingLoan
{
    public class FinancialLeasingLoanDAL
    {
        private SQLDataAccess db;
        public FinancialLeasingLoanDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getTOP1()
        {
            return db.exeSelect("Select TOP 1 * from FinancialLeasingLoan order by FinancialLeasingLoanID desc");
        }
        public DataTable getall(int langid)
        {
            return db.exeSelect("Select * from FinancialLeasingLoan sa join FinancialLeasingLoanLang hl on sa.FinancialLeasingLoanID = hl.FinancialLeasingLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join Language la on hl.LangID = la.ID  where hl.LangID = " + langid + " and cl.LangID = " + langid + "");
        }
        public DataTable getFinancialLeasingLoanrelati(int CompanyID, int FinancialLeasingLoanID, int langid)
        {
            return db.exeSelect("select * from FinancialLeasingLoan ca join FinancialLeasingLoanLang hl on ca.FinancialLeasingLoanID = hl.FinancialLeasingLoanID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.CompanyID = " + CompanyID + " and ca.FinancialLeasingLoanID != " + FinancialLeasingLoanID + " and Display = 1 and cl.LangID = " + langid + " and hl.LangID = " + langid + "");
        }
        public DataTable getFinancialLeasingLoan(int planid, string balance, int years, int langid)
        {
            return db.exeSelect("Select * from FinancialLeasingLoan sa join FinancialLeasingLoanLang hl on sa.FinancialLeasingLoanID = hl.FinancialLeasingLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableFinancialLeasingLoanInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanPlanID = " + planid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and ta.Years = " + years + " and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + "");
        }
        public DataTable getFinancialLeasingLoanByProvider(int planid, string balance, int years, int CompanyID, int langid)
        {
            return db.exeSelect("Select * from FinancialLeasingLoan sa join FinancialLeasingLoanLang hl on sa.FinancialLeasingLoanID = hl.FinancialLeasingLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableFinancialLeasingLoanInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanPlanID = " + planid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and ta.Years = " + years + " and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getFinancialLeasingLoanDefault(int langid)
        {
            return db.exeSelect("Select * from FinancialLeasingLoan sa join FinancialLeasingLoanLang hl on sa.FinancialLeasingLoanID = hl.FinancialLeasingLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableFinancialLeasingLoanInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanPlanID = 2 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and ta.Years = 1 and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + "");
        }
        public DataTable getFinancialLeasingLoanDefaultByProvider(int CompanyID, int langid)
        {
            return db.exeSelect("Select * from FinancialLeasingLoan sa join FinancialLeasingLoanLang hl on sa.FinancialLeasingLoanID = hl.FinancialLeasingLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableFinancialLeasingLoanInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.HomeLoanPlanID = 2 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and ta.Years = 1 and Display = 1 and hl.LangID= " + langid + " and cl.LangID = " + langid + " and sa.CompanyID=" + CompanyID + "");
        }
        public DataTable getFinancialLeasingLoanByID(int FinancialLeasingLoanID, int langid)
        {
            return db.exeSelect("select * from FinancialLeasingLoan ca join FinancialLeasingLoanLang hl on ca.FinancialLeasingLoanID = hl.FinancialLeasingLoanID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.FinancialLeasingLoanID = " + FinancialLeasingLoanID + " and cl.LangID = " + langid + " and hl.LangID = " + langid + "");
        }
        public DataTable getFinancialLeasingLoanByIDManager(int FinancialLeasingLoanID, int langid)
        {
            return db.exeSelect("select * from FinancialLeasingLoan ca join FinancialLeasingLoanLang hl on ca.FinancialLeasingLoanID = hl.FinancialLeasingLoanID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where ca.FinancialLeasingLoanID = " + FinancialLeasingLoanID + " and cl.LangID = " + langid + " and hl.LangID = " + langid + "");
        }
        public int UpdateFinancialLeasingLoan(FinancialLeasingLoanModels models)
        {
            return db.exeUpdate("update FinancialLeasingLoan set FinancialLeasingLoanName = N'" + models.FinancialLeasingLoanName + "', MinimumBalance=" + double.Parse(models.MinimumBalance) + ",MaximumBalance=" + double.Parse(models.MaximumBalance) + ",HomeLoanPlanID=" + models.HomeLoanPlanID + ",CompanyID= " + models.CompanyID + ",Display='" + models.Display + "' where FinancialLeasingLoanID = " + models.FinancialLeasingLoanID + "");
        }
        public int InsertFinancialLeasingLoan(FinancialLeasingLoanModels models)
        {
            return db.exeUpdate("insert into FinancialLeasingLoan values (N'" + models.FinancialLeasingLoanName + "'," + models.CompanyID + "," + double.Parse(models.MinimumBalance) + "," + double.Parse(models.MaximumBalance) + "," + models.HomeLoanPlanID + ",'" + models.Display + "')");
        }
        public int DeleteFinancialLeasingLoan(int FinancialLeasingLoanID)
        {
            return db.exeUpdate("Delete from FinancialLeasingLoan where FinancialLeasingLoanID = " + FinancialLeasingLoanID + "");
        }
        public int UpdateFinancialLeasingLoanLang(FinancialLeasingLoanModels models, int langid)
        {
            return db.exeUpdate("update FinancialLeasingLoanLang set FinancialLeasingLoanName = N'" + models.FinancialLeasingLoanName + "', Benefit = N'" + models.Benefit + "',Description=N'" + models.Description + "',FeeContent=N'" + models.FeeContent + "',MoreInfo=N'" + models.MoreInfo + "' where FinancialLeasingLoanID = " + models.FinancialLeasingLoanID + " and LangID=" + langid + "");
        }
        public int InsertFinancialLeasingLoanLang(FinancialLeasingLoanModels models, int langid)
        {
            return db.exeUpdate("insert into FinancialLeasingLoanLang values (" + models.FinancialLeasingLoanID + "," + langid + ",N'" + models.FinancialLeasingLoanName + "',N'" + models.Benefit + "',N'" + models.Description + "',N'" + models.FeeContent + "',N'" + models.MoreInfo + "')");
        }

        public int DeleteFinancialLeasingLoanLang(int FinancialLeasingLoanID)
        {
            return db.exeUpdate("Delete from FinancialLeasingLoanLang where FinancialLeasingLoanID = " + FinancialLeasingLoanID + "");
        }

        public int DeleteFinancialLeasingLoanApply(int FinancialLeasingLoanApplyID)
        {
            return db.exeUpdate("Delete from FinancialLeasingLoanApply where FinancialLeasingLoanApplyID = " + FinancialLeasingLoanApplyID + "");
        }
        public DataTable getcontractbyid(int FinancialLeasingLoanapplyid)
        {
            return db.exeSelect("select * from FinancialLeasingLoanApply ca join [FinancialLeasingLoan] c on ca.FinancialLeasingLoanID = c.FinancialLeasingLoanID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.FinancialLeasingLoanApplyID = " + FinancialLeasingLoanapplyid + " and cl.LangID = 1");
        }
        public DataTable getcontract()
        {
            return db.exeSelect("select * from FinancialLeasingLoanApply ca join [FinancialLeasingLoan] c on ca.FinancialLeasingLoanID = c.FinancialLeasingLoanID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1");
        }
        public DataTable getcontractbystatuscount(int statusid)
        {
            return db.exeSelect("select * from FinancialLeasingLoanApply ca join [FinancialLeasingLoan] c on ca.FinancialLeasingLoanID = c.FinancialLeasingLoanID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.StatusID = " + statusid + " and cl.LangID = 1");
        }
        public int FinancialLeasingLoanApply(int FinancialLeasingLoanID, FinancialLeasingLoanApplyModels app)
        {
            return db.exeUpdate("insert into FinancialLeasingLoanApply(FinancialLeasingLoanID,CustomerName,CustomerEmail,CustomerPhone,CustomerIncome,CustomerDOB,StatusID) values (" + FinancialLeasingLoanID + ",N'" + app.CustomerName + "',N'" + app.CustomerEmail + "',N'" + app.CustomerPhone + "',N'" + app.CustomerIncome + "',N'" + app.CustomerDOB + "'," + app.StatusID + ")");
        }
        public int UpdateStatus(FinancialLeasingLoanApplyModels FinancialLeasingLoan)
        {
            return db.exeUpdate("update FinancialLeasingLoanApply set StatusID= " + FinancialLeasingLoan.StatusID + " where FinancialLeasingLoanApplyID = " + FinancialLeasingLoan.FinancialLeasingLoanApplyID + "");
        }
    }
}
